﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using System.Data.SqlClient;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class NonWorkingJobSiteDAL : INonWorkingJobSiteDAL
    {
       IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
       
       public NonWorkingJobSiteDAL()
        {

        }
       public IList<NonWorkingJobSiteInfo> ListNonWorkingJobSites(NonWorkingJobSiteInfo nwjsInfo)
       {
           string strSQL;
           int NwdSequence = 0;
           int JobSite = 0;

           if (nwjsInfo.NonWorkingDay != null)
               NwdSequence = nwjsInfo.NonWorkingDay.Sequence;
           if (nwjsInfo.JobSite != null)
               JobSite = (int) nwjsInfo.JobSite.Sequence;

           List<NonWorkingJobSiteInfo> infoList = new List<NonWorkingJobSiteInfo>();

           try
           {
               dbManager.Open();
               dbManager.CreateParameters(4);

               dbManager.AddParameters(0, "@Sequence", nwjsInfo.Sequence);
               dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
               dbManager.AddParameters(2, "@NwdSequence", NwdSequence);
               dbManager.AddParameters(3, "@JobSite", JobSite);
 
               strSQL = "SELECT nj.NWD_JOB_SITE_SEQ, nj.NON_WORKING_SEQ, nj.JOB_SITE_SEQ, nj.REMARKS"
                        + ", nj.RECORD_STATUS, nj.CREATED_BY, nj.UPDATED_BY"
                   + " FROM HRM_PS_NWD_JOB_SITES nj"
                   + " WHERE nj.RECORD_STATUS not like @DeleteRecord"
                   + (nwjsInfo.Sequence > 0 ? " AND nj.NWD_JOB_SITE_SEQ = @Sequence" : "")
                   + (NwdSequence > 0 ? " AND nj.NON_WORKING_SEQ = @NwdSequence" : "")
                   + (JobSite > 0 ? " AND nj.JOB_SITE_SEQ = @JobSite" : "");

               IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

               while (rdr.Read())
               {
                   NonWorkingDayDAL nwdDAL = new NonWorkingDayDAL();
                   NonWorkingDayInfo nwdInfo = new NonWorkingDayInfo();
                   if (!rdr.IsDBNull(1))
                   {
                       IList<NonWorkingDayInfo> nwdList = nwdDAL.ListNonWorkingDay(new NonWorkingDayInfo(rdr.GetInt32(1)));
                       nwdInfo = nwdList[0];
                   }
                   JobSiteDAL jsDAL = new JobSiteDAL();
                   JobSiteInfo jsInfo = new JobSiteInfo();
                   if (!rdr.IsDBNull(2))
                   {
                       IList<JobSiteInfo> jsList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(2)));
                       jsInfo = jsList[0];
                   }

                   NonWorkingJobSiteInfo Info = new NonWorkingJobSiteInfo(rdr.GetInt32(0)
                        , nwdInfo, jsInfo
                        , rdr.IsDBNull(3) ? "" : rdr.GetString(3)
                        , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                        , rdr.IsDBNull(5) ? (int?)null : rdr.GetInt32(5)
                        , rdr.IsDBNull(6) ? (int?)null : rdr.GetInt32(6)
                        );
                   infoList.Add(Info);
               }
               return infoList;
           }
           catch
           {
               return null;
               throw;
           }
           finally
           {
               dbManager.CloseReader();
               dbManager.Dispose();
           }
        }       

        public void Insert(NonWorkingJobSiteInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@NonWorkingSeq", Info.NonWorkingDay.Sequence);
                dbManager.AddParameters(1, "@JobSiteSeq", Info.JobSite == null ? (int?)null : Info.JobSite.Sequence);
                dbManager.AddParameters(2, "@Remarks", Info.Remarks);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(5, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_NWD_JOB_SITES(NON_WORKING_SEQ, JOB_SITE_SEQ"
                        + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@NonWorkingSeq, @JobSiteSeq, @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(NonWorkingJobSiteInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@NwdJobSiteSeq", Info.Sequence);
                dbManager.AddParameters(1, "@JobSiteSeq", Info.JobSite == null ? (int?)null : Info.JobSite.Sequence);
                dbManager.AddParameters(2, "@Remarks", Info.Remarks);
                dbManager.AddParameters(3, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(4, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_NWD_JOB_SITES"
                           + " SET REMARKS = @Remarks"
                                + ", JOB_SITE_SEQ = @JobSiteSeq"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                           + " WHERE NWD_JOB_SITE_SEQ = @NwdJobSiteSeq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdateWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_NWD_JOB_SITES"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdateWhen"
                    + " WHERE NWD_JOB_SITE_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
